﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using StateGrid.MDBservice;
using System.IO;
using iTextSharp.text.pdf;

namespace StateGrid.WWQK
{
    class WWQK_MDB : MDBservice.MDBservice
    {
        public override String id
        {
            get
            {
                return "waiwenqikan";
            }
        }

        private WWQK_DAO wwqkDAO = new WWQK_DAO();

        public override void clear()
        {
            base.clear();
        }

        public override String getTest()
        {
            IList<WWQK_FL> r = wwqkDAO.getAllWwqkFL();
            String ret = String.Empty;
            foreach (WWQK_FL i in r)
            {
                ret += i.ToString();
            }
            return ret;
        }

        public WWQK_MDB()
        {
            pmTableName = "WWQK_WF";
            formatPmTableName = "WWQK";
            title = "外文期刊";
        }

        public override void setColumnList()
        {
            this.ColumnList = new List<Column>();
            if (hasFormatTable)
            {
                this.ColumnList.Add(new Column("索引编码", OleDbType.WChar, 20));
                this.ColumnList.Add(new Column("文件名", OleDbType.WChar, 30));
                this.ColumnList.Add(new Column("原文件名", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("年份", OleDbType.WChar, 4));
                this.ColumnList.Add(new Column("期号", OleDbType.WChar, 2));
                this.ColumnList.Add(new Column("中文篇名", OleDbType.WChar, 150));
                this.ColumnList.Add(new Column("外文篇名", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("作者", OleDbType.WChar, 150));
                this.ColumnList.Add(new Column("作者单位", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("开本", OleDbType.WChar, 4));
                this.ColumnList.Add(new Column("页数", OleDbType.SmallInt));
                this.ColumnList.Add(new Column("中文文摘", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("外文文摘", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("中文主题词", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("外文主题词", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("国图分类", OleDbType.WChar, 30));
                this.ColumnList.Add(new Column("文献标识", OleDbType.WChar, 4));
                this.ColumnList.Add(new Column("登录人", OleDbType.WChar, 4));
                this.ColumnList.Add(new Column("更新日期", OleDbType.Date));
            }
            else
            {
                this.ColumnList.Add(new Column("英文刊名", OleDbType.WChar, 0)); //原文件名
                this.ColumnList.Add(new Column("PDF全文", OleDbType.WChar, 0)); //原文件名
                this.ColumnList.Add(new Column("年", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("期", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("论文标题", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("英文标题E", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("作者", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("作者单位", OleDbType.WChar, 0));
                //this.ColumnList.Add(new Column("开本", OleDbType.WChar, 0));  //如何算？？
                this.ColumnList.Add(new Column("页码", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("摘要", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("英文摘要", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("关键词", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("英文关键词", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("分类号", OleDbType.WChar, 0));
            }
        }

        protected override PM getPM(OleDbDataReader reader, out String err)
        {
            err = String.Empty;
            WWQK_PM pm = new WWQK_PM();
            if (hasFormatTable)
            {
                pm.sybm = reader["索引编码"].ToString();
                pm.wjm = reader["文件名"].ToString();
                pm.ywjm = reader["原文件名"].ToString();
                pm.nf = reader["年份"].ToString();
                pm.qh = reader["期号"].ToString();
                pm.zwpm = reader["中文篇名"].ToString();
                pm.wwpm = reader["外文篇名"].ToString();
                pm.zz = reader["作者"].ToString();
                pm.zzdw = reader["作者单位"].ToString();
                pm.kb = reader["开本"].ToString();
                try
                {
                    pm.ys = int.Parse(reader["页数"].ToString());
                }
                catch (Exception)
                {
                    pm.ys = 0;
                    err += pm.ywjm + ":" + pm.wwpm + ": 页数\"" + reader["页数"] + "\"出错，置为0\n";
                }
                pm.zwwz = reader["中文文摘"].ToString();
                pm.wwwz = reader["外文文摘"].ToString();
                pm.zwztc = reader["中文主题词"].ToString();
                pm.wwztc = reader["外文主题词"].ToString();
                pm.gtfl = reader["国图分类"].ToString();
                pm.wxbs = reader["文献标识"].ToString();
            }
            else
            {
                IList<WWQK_SYML> allSYML = wwqkDAO.getAllWwqkSYML();
                String KanMing = reader["英文刊名"].ToString();
                pm.sybm = null;
                foreach (WWQK_SYML syml in allSYML)
                {
                    if (KanMing.Equals(syml.wwmc))
                    {
                        pm.sybm = syml.sybm;
                        break;
                    }
                }
                if (pm.sybm == null)
                {
                    err += "刊名《" + KanMing + "》不存在";
                }
                //pm.sybm = reader["索引编码"].ToString();
                //pm.wjm = reader["文件名"].ToString();
                pm.ywjm = reader["PDF全文"].ToString();
                pm.nf = reader["年"].ToString();
                pm.qh = reader["期"].ToString();
                pm.zwpm = reader["论文标题"].ToString();
                pm.wwpm = reader["英文标题E"].ToString();
                pm.zz = reader["作者"].ToString();
                pm.zzdw = reader["作者单位"].ToString();
                //pm.kb = reader["开本"].ToString();
                pm.kb = "";
                pm.zwwz = reader["摘要"].ToString();
                pm.wwwz = reader["英文摘要"].ToString();
                pm.zwztc = reader["关键词"].ToString();
                pm.wwztc = reader["英文关键词"].ToString();
                pm.gtfl = reader["分类号"].ToString();
                //pm.wxbs = reader["文献标识"].ToString();
                pm.wxbs = "";
            }
            pm.dlr = reader["登录人"].ToString();
            pm.fillEmptyStringAsNull();
            return pm;
        }

        public override bool checkPMValid(PM pm, out string errMsg)
        {
            errMsg = String.Empty;
            WWQK_PM wwqk = (WWQK_PM)pm;
            this.openOleDbConnection();
            sqlcmd.CommandText = @"select * from " + formatPmTableName + " where 索引编码=@索引编码 and 年份=@年份 and 期号=@期号 and 外文篇名=@外文篇名";
            sqlcmd.Connection = this.ObjConnection;
            sqlcmd.Parameters.Add(new OleDbParameter("索引编码", wwqk.sybm));
            sqlcmd.Parameters.Add(new OleDbParameter("年份", wwqk.nf));
            sqlcmd.Parameters.Add(new OleDbParameter("期号", wwqk.qh));
            sqlcmd.Parameters.Add(new OleDbParameter("外文篇名", wwqk.wwpm));
            OleDbDataReader reader = sqlcmd.ExecuteReader();

            if (reader.Read())
            {
                reader.Close();
                sqlcmd.Parameters.Clear();
                return true;
            }
            else
            {
                reader.Close();
                sqlcmd.Parameters.Clear();
                return false;
            }
            //            this.closeOleDbConnection();
        }

        public override String getHash()
        {
            String err = String.Empty;
            IList<WWQK_SYML> temp = null;
            try
            {
                temp = wwqkDAO.getAllWwqkSYML();
            }
            catch (Exception e)
            {
                err = "错误：" + e.Message + "\n";
                return err;
            }
            if (Hash == null)
                Hash = new Hashtable();
            Hash.Clear();
            foreach (WWQK_SYML syml in temp)
            {
                Hash.Add(syml.sybm, syml);
            }
            return err;
        }
        public override string formatData(PM pm)
        {
            //反馈信息
            String result = String.Empty;
            String Empty = "NULL";
            WWQK_PM wwqk = (WWQK_PM)pm;

            wwqk.zz = GlobalMethod.Semicolon(wwqk.zz);
            wwqk.zzdw = GlobalMethod.Semicolon(wwqk.zzdw);
            wwqk.zwztc = GlobalMethod.Semicolon(wwqk.zwztc);
            wwqk.wwztc = GlobalMethod.Semicolon(wwqk.wwztc);
            wwqk.gtfl = GlobalMethod.Semicolon(wwqk.gtfl);
            wwqk.wjm = "自动生成";
            wwqk.dlr = user.name;
            wwqk.gxrq = DateTime.Now;

            if (Empty.Equals(wwqk.ywjm))
            {
                result += "原文件名（PDF全文）为空 ";
            }
            else
            {
                if (!File.Exists(fileFolder + wwqk.ywjm))
                {
                    result += wwqk.ywjm + " PDF文件不存在";
                }
                else
                {
                    PdfReader pdf = new PdfReader(fileFolder + wwqk.ywjm);
                    if (null != pdf)
                    {
                        wwqk.ys = pdf.NumberOfPages;
                    }
                    else
                    {
                        wwqk.ys = 0;
                    }
                    pdf.Close();
                }
            }
            if (Empty.Equals(wwqk.wwpm))
            {
                result += "外文篇名为空 ";
            }
            if (Empty.Equals(wwqk.nf))
            {
                result += "年份为空 ";
            }
            if (Empty.Equals(wwqk.qh))
            {
                result += "期号为空 ";
            }
            if (!result.Equals(String.Empty))
            {
                result += " : " + wwqk.nf + "年" + wwqk.qh + "期《" + wwqk.wwpm + "》\n";
            }

            return result;
        }

        public override String insertPM(PM pm)
        {
            WWQK_PM wwqk = (WWQK_PM)pm;
            int back;
            String err = String.Empty;
            //打开连接
            this.openOleDbConnection();
            OleDbCommand sqlcmd = new OleDbCommand();
            if (!String.Empty.Equals(validateTableName(formatPmTableName)))
            {
                //TODO: 新建错误表
                sqlcmd.CommandText = @"create Table " + formatPmTableName + @"(索引编码 char(20), 文件名 char(30), 原文件名 char(255), 年份 char(4), 期号 char(2), 中文篇名 char(150), 外文篇名 char(255), 作者 char(150), 作者单位 char(255), 开本 char(4), 页数 smallInt, 中文文摘 text, 外文文摘 text, 中文主题词 char(255), 外文主题词 char(255), 国图分类 char(30), 文献标识 char(4), 登录人 char(4), 更新日期 datetime)";
                sqlcmd.Connection = this.ObjConnection;
                try
                {
                    back = sqlcmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    err = e.Message;
                    return err;
                }
            }

            //sql语句
            sqlcmd.CommandText = @"insert into " + (formatPmTableName) + @"(索引编码,文件名,原文件名,年份,期号,中文篇名,外文篇名,作者,作者单位,开本,页数,中文文摘,外文文摘,中文主题词,外文主题词,国图分类,文献标识,登录人,更新日期) values(@索引编码,@文件名,@原文件名,@年份,@期号,@中文篇名,@外文篇名,@作者,@作者单位,@开本,@页数,@中文文摘,@外文文摘,@中文主题词,@外文主题词,@国图分类,@文献标识,@登录人,@更新日期)";
            sqlcmd.Connection = this.ObjConnection;
            sqlcmd.Parameters.Add(new OleDbParameter("索引编码", wwqk.sybm));
            sqlcmd.Parameters.Add(new OleDbParameter("文件名", wwqk.wjm));
            sqlcmd.Parameters.Add(new OleDbParameter("原文件名", wwqk.ywjm));
            sqlcmd.Parameters.Add(new OleDbParameter("年份", wwqk.nf));
            sqlcmd.Parameters.Add(new OleDbParameter("期号", wwqk.qh));
            sqlcmd.Parameters.Add(new OleDbParameter("中文篇名", wwqk.zwpm));
            sqlcmd.Parameters.Add(new OleDbParameter("外文篇名", wwqk.wwpm));
            sqlcmd.Parameters.Add(new OleDbParameter("作者", wwqk.zz));
            sqlcmd.Parameters.Add(new OleDbParameter("作者单位", wwqk.zzdw));
            sqlcmd.Parameters.Add(new OleDbParameter("开本", wwqk.kb));
            sqlcmd.Parameters.Add(new OleDbParameter("页数", wwqk.ys));
            sqlcmd.Parameters.Add(new OleDbParameter("中文文摘", wwqk.zwwz));
            sqlcmd.Parameters.Add(new OleDbParameter("外文文摘", wwqk.wwwz));
            sqlcmd.Parameters.Add(new OleDbParameter("中文主题词", wwqk.zwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("外文主题词", wwqk.wwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("国图分类", wwqk.gtfl));
            sqlcmd.Parameters.Add(new OleDbParameter("文献标识", wwqk.wxbs));
            sqlcmd.Parameters.Add(new OleDbParameter("登录人", wwqk.dlr));
            OleDbParameter parameter = new OleDbParameter("更新日期", OleDbType.DBDate);
            parameter.Value = wwqk.gxrq;
            sqlcmd.Parameters.Add(parameter);
            try
            {
                back = sqlcmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                err = e.Message;
            }
            sqlcmd.Parameters.Clear();

            this.closeOleDbConnection();
            return err;
        }

        public override int deletePM(PM pm, out String err)
        {
            int back = 0;
            err = String.Empty;
            WWQK_PM wwqk = (WWQK_PM)pm;
            //打开连接
            this.openOleDbConnection();
            OleDbCommand sqlcmd = new OleDbCommand();
            if (!String.Empty.Equals(validateTableName(formatPmTableName)))
            {
                //TODO: 新建错误表
                sqlcmd.CommandText = @"create Table " + formatPmTableName + @"(索引编码 char(20), 文件名 char(30), 原文件名 char(255), 年份 char(4), 期号 char(2), 中文篇名 char(150), 外文篇名 char(255), 作者 char(150), 作者单位 char(255), 开本 char(4), 页数 smallInt, 中文文摘 text, 外文文摘 text, 中文主题词 char(255), 外文主题词 char(255), 国图分类 char(30), 文献标识 char(4), 登录人 char(4), 更新日期 datetime)";
                sqlcmd.Connection = this.ObjConnection;
                try
                {
                    back = sqlcmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    err = e.Message;
                    return 0;
                }
            }

            //sql语句
            sqlcmd.CommandText = @"delete from " + formatPmTableName + " where 索引编码=@索引编码 and 年份=@年份 and 期号=@期号 and 外文篇名=@外文篇名";
            sqlcmd.Connection = this.ObjConnection;
            sqlcmd.Parameters.Add(new OleDbParameter("索引编码", wwqk.sybm));
            sqlcmd.Parameters.Add(new OleDbParameter("年份", wwqk.nf));
            sqlcmd.Parameters.Add(new OleDbParameter("期号", wwqk.qh));
            sqlcmd.Parameters.Add(new OleDbParameter("外文篇名", wwqk.wwpm));

            try
            {
                back = sqlcmd.ExecuteNonQuery();    //TODO:此back不是函数作用域的back？
            }
            catch (Exception e)
            {
                err = "Exception: " + e.Message;
            }
            sqlcmd.Parameters.Clear();
            //            this.closeOleDbConnection();
            return back;
        }
        /* 现在的方法先delete再insert，就不用为每文库写update了
        public override bool updatePM(PM pm)
        {
            ZWQK_PM zwqk = (ZWQK_PM)pm;
            //打开连接
            this.openOleDbConnection();

            //sql语句
            OleDbCommand sqlcmd = new OleDbCommand(@"update ZWQK set 作者=@作者,作者单位=@作者单位,中文主题词=@中文主题词,外文主题词=@外文主题词,国图分类=@国图分类 where 中文篇名=@中文篇名 and 文件名=@文件名", this.ObjConnection);

            sqlcmd.Parameters.Add(new OleDbParameter("作者", zwqk.zz));
            sqlcmd.Parameters.Add(new OleDbParameter("作者单位", zwqk.zzdw));
            sqlcmd.Parameters.Add(new OleDbParameter("中文主题词", zwqk.zwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("外文主题词", zwqk.wwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("国图分类", zwqk.gtfl));
            sqlcmd.Parameters.Add(new OleDbParameter("中文篇名", zwqk.zwpm));
            sqlcmd.Parameters.Add(new OleDbParameter("文件名", zwqk.wjm));
            int back = sqlcmd.ExecuteNonQuery();
            if (back == 0)
            {
                return false;
            }
            //objConnection.Close();
            return true;
        }*/

        public override bool findServerDuplicate(PM pm)
        {
            WWQK_PM wwqk = (WWQK_PM)pm;
            IList<WWQK_PM> result = wwqkDAO.queryWwqkPM(wwqk.sybm, wwqk.nf, wwqk.qh, wwqk.wwpm);
            if (result != null && result.Count != 0)
                return true;
            else
                return false;
        }

        public override String insertPMwithUpdateWJM(PM pm)
        {
            String err = String.Empty;
            WWQK_PM wwqk = (WWQK_PM)pm;
            int index;
            index = wwqk.ywjm.LastIndexOf('/');
            int serial;
            if ("ml".Equals(wwqk.ywjm.Substring(index + 1, 2)))
            {
                serial = 0;
            }
            else
            {

                WWQK_PM maxPm = wwqkDAO.getMaxWwqkPM(wwqk.sybm, wwqk.nf, wwqk.qh);
                if (maxPm != null)
                {
                    int pos = maxPm.wjm.LastIndexOf('-');
                    int pos2 = maxPm.wjm.LastIndexOf('.');
                    serial = int.Parse(maxPm.wjm.Substring(pos + 1, 3)) + 1;
                }
                else
                {
                    serial = 1;
                }
            }
            index = wwqk.ywjm.LastIndexOf('.');
            String suffixStr;
            if (index >= 0)
                suffixStr = wwqk.ywjm.Substring(index);
            else
                suffixStr = String.Empty;
            String serialStr = "0000" + serial.ToString();
            wwqk.wjm = wwqk.sybm + "-" + wwqk.nf + "-" + wwqk.qh + "-" + serialStr.Substring(serialStr.Length - 3) + suffixStr;
            try
            {
                wwqkDAO.insertWwqkPM(wwqk);
            }
            catch (Exception e)
            {
                err += e.Message + "\n";
            }
            return err;
        }

        public override string addOneRecord(PM pm)
        {
            String err = String.Empty;
            WWQK_PM wwqk = (WWQK_PM)pm;
            wwqk.gxrq = DateTime.Now;
            wwqk.eraseNullInEmptyString();
            err += insertPMwithUpdateWJM(pm);
            if (!String.Empty.Equals(err))
                return err;
            String path = "QK/";
            path += wwqk.sybm.Substring(0, 2) + "/";
            path += wwqk.sybm.Substring(2, 2) + "/";
            path += wwqk.sybm.Substring(4, 2) + "/";
            path += wwqk.sybm.Substring(6, 2) + "/";
            path += wwqk.nf.ToString() + "/";
            path += wwqk.qh.ToString() + "/";

            String srcFile = fileFolder + wwqk.ywjm;
            String destFile = path + wwqk.wjm;
            try
            {
                FTPtransfer.uploadAndCreateDir(destFile, srcFile);
                System.IO.File.Delete(srcFile);
            }
            catch (Exception e)
            {
                err += "Exception: " + e.Message;
                wwqkDAO.deleteWwqkPMByWjm(wwqk.wjm, user.name);
            }
            return err;
        }
    }
}
